#include "dbhelper.h"
#include "QSqlQuery"
#include "QMap"
#include "toolhelper.h"
#include "QList"
#include "QVariantMap"
#include "QSqlRecord"
#include "errorlog.h"
#include "QDate"
//#include "iostream"

dbhelper::dbhelper()
{

}

bool dbhelper::GetConnection()
{
    if(QSqlDatabase::contains("defaultConn"))
        _db = QSqlDatabase::database("defaultConn");
    else
        _db = QSqlDatabase::addDatabase("QSQLITE","defaultConn");
    _db.setDatabaseName("testdb");
    bool s = _db.open();
    return s;
}

bool dbhelper::GetConnection(QString dbName)
{
    if(QSqlDatabase::contains("defaultConn")){
        _db = QSqlDatabase::database("defaultConn");
        qDebug("134");
    }
    else
    {
        _db = QSqlDatabase::addDatabase("QSQLITE","defaultConn");
    }
    _db.setDatabaseName(dbName);
    bool s = _db.open();
    return s;
}

void dbhelper::DisConnection()
{
    QString dbName = _db.databaseName();
    if(_db.isOpen())
    {
        _db.close();
        QSqlDatabase::removeDatabase(dbName);
    }
}

bool dbhelper::InitDataBase()
{
    QSqlQuery query(_db);
    bool commit = true;

    QStringList tl = _db.tables();
    if(tl.count()!=0)return true;

    QFile file(":/pdm.sql");
    if(file.open(QIODevice::ReadOnly|QIODevice::Text)){
        QString script = file.readAll();
        QStringList ql = script.split(";",QString::SkipEmptyParts);
        if(_db.transaction())
        {
            foreach(QString q,ql)
            {
                if(!query.exec(q))
                {
                    commit = false;
                    ErrorLog() << query.lastError().text();
                    break;
                }
            }
            if(commit)
            {
                _db.commit();
            }
            else {
                _db.rollback();
            }
        }
        file.close();
    }
    DisConnection();
    return commit;
}

int dbhelper::Insert(QString table, QVariantMap map)
{

    QSqlQuery query(_db);
    QString strSql = "Insert Into " + table;
    QStringList fields;
    foreach(QString key,map.keys())
    {
        fields<<key;
    }

    QStringList values;
    foreach(QString key,map.keys())
    {

        QString s = ":" + ToolHelper::GetSubString(key,0,1);
        values<<s;
    }

    strSql = strSql + "("+fields.join(",")+") Values("+values.join(",")+")";
    query.prepare(strSql);
    //std::cout<<strSql.toStdString()<<std::endl;
    foreach(QString key,map.keys())
    {
        QString s = ":" + ToolHelper::GetSubString(key,0,1);
        query.bindValue(s,map[key]);
    }

    if(query.exec())return query.lastInsertId().toInt();
    else
    {
        ErrorLog()<<query.lastError().text();
        return -1;
    }
}

bool dbhelper::Update(QString table,QVariantMap map,QString filter)
{
    QSqlQuery query(_db);
    QString strSql = "Update " + table + " Set ";
    QStringList upfields;
    foreach(QString key,map.keys())
    {
        QString s = key+"=:" + ToolHelper::GetSubString(key,0,1);
        upfields<<s;
    }
    strSql = strSql + upfields.join(",");
    if(!filter.isEmpty())
    {
        strSql += " Where "+filter;
    }
    query.prepare(strSql);
    //std::cout<<strSql.toStdString()<<std::endl;
    foreach(QString key,map.keys())
    {
        QString s = ":" + ToolHelper::GetSubString(key,0,1);
        query.bindValue(s,map[key]);
    }

    bool ret = query.exec();
    if(!ret) ErrorLog()<<query.lastError().text();
    return ret;
}

bool dbhelper::Delete(QString table, QString filter)
{
    QSqlQuery query(_db);
    QString strSql = "Delete From " + table;
    if(!filter.isEmpty()) strSql += " Where " + filter;
    query.prepare(strSql);
    bool ret = query.exec();
    if(!ret) ErrorLog()<<query.lastError().text();
    return ret;
}

QList<QVariantMap> dbhelper::SimpleSelect(QString table,QStringList columns,QVariantMap map, QString otherFilter)
{
    QString strSql = "SELECT "+columns.join(",") + " FROM " + table;

    if(!map.isEmpty()){
        QStringList sfields;
        foreach(QString key,map.keys())
        {
            QString s = key+"=:" + ToolHelper::GetSubString(key,0,1);
            sfields<<s;
        }
        strSql += " WHERE " + sfields.join(" AND ");
    }
    if(!otherFilter.isEmpty())
    {
        if(map.isEmpty())
        {
            strSql += " WHERE " + otherFilter;
        }
        else
        {
            strSql += " AND " + otherFilter;
        }
    }

    //std::cout<<strSql.toStdString()<<std::endl;
    QSqlQuery query(strSql,_db);

    foreach(QString key,map.keys())
    {
        QString s = ":" + ToolHelper::GetSubString(key,0,1);
        query.bindValue(s,map[key]);
    }

    if(query.exec())
    {
        QList<QVariantMap> list;
        while(query.next())
        {
            QSqlRecord record = query.record();
            list.append(ToolHelper::Convert(record,columns));
        }
        return list;
    }
    else{
        ErrorLog()<<query.lastError().text();
        return QList<QVariantMap>();
    }
}

QList<QVariantMap> dbhelper::CustomSelect(QString strSql, QVariantMap map)
{
    QSqlQuery query(strSql,_db);
    foreach(QString k,map.keys())
    {
        query.bindValue(":"+k,map[k]);
    }
    if(query.exec())
    {
        QList<QVariantMap> list;
        while(query.next())
        {
            QSqlRecord record = query.record();
            list.append(ToolHelper::Convert(record,QStringList("*")));
        }
        return list;
    }
    else{
        ErrorLog()<<query.lastError().text();
        return QList<QVariantMap>();
    }
}

dbhelper::~dbhelper()
{
   // _db->close();
}
